1. Field of the Invention
This invention relates in general to database management systems performed by computers, and in particular, to the optimization of correlated queries using automatic summary tables (ASTs).
2. Description of Related Art
Computer systems incorporating Relational DataBase Management System (RDBMS) software using Structured Query Language (SQL) interface are well known in the art. The SQL interface has evolved into a standard language for RDBMS software and has been adopted as such by both the American Nationals Standard Institute (ANSI) and the International Standards Organization (ISO).
For most RDBMS software, combinations of tables and views are used to access data stored in tables in the database. Indices are often used to improve the performance of retrieving data from tables. However, indices are generally limited to columns from base tables. Thus, indices are not seen as suitable for:                results of aggregations,        results of joins for commonly used subsets of the data, and        results of subqueries.        
A view definition includes a query that, if processed, provides a temporary result table based on the results of the query at that point in time. Using an INSERT statement and an appropriately defined table in the database, the temporary results table can be stored in the database. To refresh this table, the user would need to perform a DELETE from the table and then perform the INSERT again.
Users can directly query against the created table, provided that the users are aware how the results were derived. Generally, the RDBMS software is not aware that such a table is any different from any other table in the database. Moreover, this table cannot be used by an optimizer within the RDBMS software to improve performance, even though the table may contain data that would drastically improve the performance of other queries.
This leads to the notion of automatic summary tables (ASTs) or materialized views as envisioned by the present invention. These tables are similar to the created table described above, except that the definition of the table is based on a “full select” (much like a view) that is materialized in the table. The columns of the table are based on the elements of the select list of the full select.
In the present invention, with properly defined summary tables, the RDBMS software is now aware how the result in the AST was derived. When an arbitrarily complex query is submitted, an optimizer in the RDBMS software can now consider using the ASTs to answer the query, which is a technique that requires performing matching and compensation between the query and summary table definition.
There are extensive research activities and literature on this topic, as disclosed in the following publications, all of which are incorporated by reference herein:
1. L. S. Colby, R. L. Cole, E. Haslam, N. Jazaeri, G. Johnson, W J. McKenna, L. Schumacher, D. Wihite. Red Brick Vista: Aggregate Computation and Management. Proceedings of the 14th Int'l. Conference on Data Engineering, Orlando, Fla., 1998.
2. R. Bello, K. Dias, A. Downing, J. Feenan, J. Finnerty, W. Norcott, H. Sun, A. Witkowski, M. Ziauddin. Materialized Views In Oracle. Proceedings of the 24th VLDB Conference, New York, 1998.
3. D. Srivastava, S. Dar, H. Jagadish, A. Levy. Answering Queries with Aggregation Using Views. Proceedings of the 22nd VLDB Conference, Mumbai, India, 1996.
4. M. Zaharioudakis, R. Cochrane, G. Lapis, H. Pirahesh, M. Urata. Answering Complex SQL Queries Using Automatic summary Tables. Proceedings of the ACM-SIGMOD Conference, Dallas, Tex., 2000.
However, the current state of the art is that SQL statements with correlation can not be used in defining summary tables, and as a result, SQL queries with correlation can not be optimized using summary tables.